{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style>.container { width:100% !important; }</style>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  variety       date  change\n",
      "0       A 2018-11-29     4.0\n",
      "1      AG 2018-11-29    37.0\n",
      "2      AL 2018-11-29   -22.0\n",
      "3      AP 2018-11-29  -358.0\n",
      "4      AU 2018-11-29     2.0\n",
      "        date variety symbol  long_openIntr  short_openIntr\n",
      "0 2018-11-29      AP     AP         9115.0          9236.0\n",
      "1 2018-11-29      AP     AP         7504.0          6772.0\n",
      "2 2018-11-29      AP     AP         6457.0          5720.0\n",
      "3 2018-11-29      AP     AP         5105.0          5698.0\n",
      "4 2018-11-29      AP     AP         5081.0          5626.0\n",
      "           variety  long_openIntr  short_openIntr      净持仓  净持仓变化量  change  \\\n",
      "date                                                                         \n",
      "2018-11-30       A        66515.0         90136.0 -23621.0  -222.0    25.0   \n",
      "2018-11-30       A        66515.0         90136.0 -23621.0  -222.0    25.0   \n",
      "2018-11-30      AL       260597.0        258506.0   2091.0  8334.0   -76.0   \n",
      "2018-11-30      AL       260597.0        258506.0   2091.0  8334.0   -76.0   \n",
      "2018-11-30      AP        91305.0         87554.0   3751.0 -1509.0   184.0   \n",
      "\n",
      "            交易信号  \n",
      "date              \n",
      "2018-11-30    -1  \n",
      "2018-11-30    -1  \n",
      "2018-11-30     1  \n",
      "2018-11-30     1  \n",
      "2018-11-30    -1  \n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "e:\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:40: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n"
     ]
    }
   ],
   "source": [
    "import pymongo\n",
    "import pandas as pd\n",
    "from pandas import Series,DataFrame\n",
    "import matplotlib as plt\n",
    "import matplotlib.dates as mdate\n",
    "from IPython.core.display import display, HTML\n",
    "display(HTML(\"<style>.container { width:100% !important; }</style>\"))\n",
    "#二行即可搞定画图中文乱码\n",
    "plt.rcParams['font.sans-serif']=['SimHei'] #用来正常显示中文标签\n",
    "plt.rcParams['axes.unicode_minus']=False #用来正常显示负号\n",
    "pd.set_option('display.width', None)  # 设置字符显示宽度\n",
    "pd.set_option('display.max_rows', None)  # 设置显示最大行\n",
    "pd.set_option('display.max_columns', None)  # 设置显示最大行\n",
    "\n",
    "#连接数据库\n",
    "client = pymongo.MongoClient('localhost',27017)\n",
    "futures = client.futures\n",
    "market = futures.market\n",
    "unit = futures.unit\n",
    "position = futures.position\n",
    "\n",
    "date='20181129'\n",
    "#加载数据\n",
    "market = DataFrame(list(market.find({'date': {'$gte':date}})))\n",
    "unit = DataFrame(list(unit.find()))\n",
    "position = DataFrame(list(position.find({'date': {'$gte': date}})))\n",
    "\n",
    "# #列名重名\n",
    "# position= position.rename(columns={'var':'variety'})\n",
    "\n",
    "#删除/选取某行含有特殊数值的列\n",
    "\n",
    "position=position.dropna()\n",
    "\n",
    "# ##选择需要显示的字段\n",
    "marketdata=market[['variety','date','change']]\n",
    "position=position[['date','variety','symbol','long_openIntr','short_openIntr']]\n",
    "\n",
    "#修改日期格式，非常方便\n",
    "marketdata['date'] = pd.to_datetime(marketdata['date'], format='%Y/%m/%d')\n",
    "position['date'] = pd.to_datetime(position['date'], format='%Y/%m/%d')\n",
    "# 值替换replace()\n",
    "# position=position.replace(['PTA'],'TA')\n",
    "#汇总合约\n",
    "netPosition=position.groupby(['date','variety'])[['long_openIntr','short_openIntr']].sum()\n",
    "\n",
    "netPosition['净持仓']=netPosition.apply(lambda x:x['long_openIntr']-x['short_openIntr'],axis=1)\n",
    "netPosition['上一日净持仓']=netPosition.groupby('variety')['净持仓'].shift(1)\n",
    "netPosition['净持仓变化量']=netPosition.apply(lambda x: x['净持仓']-x['上一日净持仓'],axis=1)\n",
    "netPosition=netPosition.dropna()\n",
    "\n",
    "marketdata.set_index(['date','variety'])\n",
    "#计算价格变化量\n",
    "\n",
    "print(marketdata.head())\n",
    "print(position.head())\n",
    "# 并集\n",
    "trade=pd.merge(netPosition,marketdata, on=['date','variety'],how='outer')\n",
    "trade = trade.dropna(axis=0, how='any')\n",
    "trade['交易信号'] = trade.apply(lambda x: 0 if x['净持仓变化量']*x['change']>=0  else 1 if x['净持仓变化量']>0 else -1,axis=1)\n",
    "trade=trade.set_index('date')\n",
    "trade = trade[trade['交易信号'] != 0]\n",
    "del trade['上一日净持仓']\n",
    "print(trade.head())\n",
    "\n",
    "\n",
    "# trade.to_csv(r\"c:\\futures1.csv\",mode='a',encoding='ANSI',header=True)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
